import signal
import sys
from concurrent import futures
from datetime import datetime
import grpc
import pymysql
from openai import OpenAI
import AI_pb2
import AI_pb2_grpc

# 异常类
class CustomError(Exception):
    def __init__(self, message):
        super().__init__(message)


client = OpenAI(
    base_url="https://api.wlai.vip/v1",
    api_key='sk-nFJ9RTmAKShz6BCSTSQmB071EtWfncPUZUymao7mFUtUEYuX',
    timeout=120
)

Model = "gpt-4o-mini" #使用的AI模型

class AIServiceServicer(AI_pb2_grpc.AIServiceServicer):
    def __init__(self):
        self.connection = pymysql.connect(host='localhost',user='root',password='123456',db='airpg')
        #self.connection = pymysql.connect(host='8.152.216.65',user='AIRPG',password='AIrpg@12',db='airpg')
        self.cursor = self.connection.cursor()

    # 第一次对话内容，初始化
    history = []

    # 第一次加载剧本
    def load_script_first(self, script_id):
        condition_script = "id = " + str(script_id)
        condition = "script_id = " + str(script_id)
        # 拿剧本
        sql_script = f"SELECT content FROM script WHERE {condition_script}"
        self.cursor.execute(sql_script)
        script_result = self.cursor.fetchone()

        # 拿角色
        sql_role = f"SELECT name, description,traits FROM role WHERE {condition}"
        self.cursor.execute(sql_role)
        role_result = self.cursor.fetchall()

        # 拿初始场景
        sql_scene = f"SELECT description FROM scene WHERE {condition}"
        self.cursor.execute(sql_scene)
        scene_result = self.cursor.fetchall()

        return script_result, role_result, scene_result

    # 第一次加载剧本
    def InitializeScript(self, request, context):
        try:
            # 加载 剧本、初始角色、 初始场景
            script_result, role_result, scene_result = self.load_script_first(request.scriptId)
            # print(script_result,role_result,scene_result)
            if not script_result :
                context.set_details('Script not found')
                context.set_code(grpc.StatusCode.NOT_FOUND)
                return AI_pb2.ScriptInitializerResponse(
                    status='0',
                    message="Script not found",
                )

            # 创建提示词
            roles_description = "\n".join([f"角色: {role[0]}, 角色描述: {role[1]}, 性格: {role[2]}" for role in role_result])
            initial_scene = scene_result[0][0]
            initial_prompt = f"{script_result[0]}\n\n{roles_description}\n\n初始场景: {initial_scene}"

            #第一次初始化
            self.history = []
            self.history.append(
                {"role": "system", "content": initial_prompt}
            )
            self.history.append(
                {"role": "user", "content": "你将扮演文字冒险游戏的叙述者和游戏主控，负责描述场景、引导玩家并响应玩家的行动。"
                                            "你需要为玩家描述当前的场景和情节。"
                                            "等待玩家输入他们的行动或选择。"
                                            "根据玩家的输入，叙述故事的进展，描述结果和新的情境。"
                                            "每次回应后，可以提示玩家可以采取的下一步行动。"
                                            "现在请你依次向用户介绍背景，角色信息和初始场景，并询问用户的下一步动作"}
            )

            ai_response = client.chat.completions.create(
                model=Model,
                messages=self.history,
            )

            assistant_response = ai_response.choices[0].message.content
            print(f"GPT: {assistant_response}")

            # 添加到history和数据库
            self.history.append(
                {"role": "system", "content": assistant_response}
            )
            self.insert_message(request.conversationID, "AI", assistant_response)


            # Construct the GRPC response
            response = AI_pb2.ScriptInitializerResponse(
                status='1',
                message=assistant_response,
            )
            return response
        except Exception as e:
            print(f"Error processing request: {e}")
            context.set_details('Error processing request')
            context.set_code(grpc.StatusCode.INTERNAL)
            return AI_pb2.ScriptInitializerResponse(
                status='1',
                message="An unexpected error occurred!",
            )

    # 多轮对话函数
    def chat(self, query, history):
        history.append({
            "role": "user",
            "content": query
        })
        completion = client.chat.completions.create(
            model=Model,
            messages=history,
        )
        result = completion.choices[0].message.content
        history.append({
            "role": "system",
            "content": result
        })
        return result

    #数据库插入函数
    def insert_message(self, conversation_id, sender, content):
        # 构建SQL插入语句
        sql_insert_message = """
            INSERT INTO message (conversation_id, sender, content, timestamp)
            VALUES (%s, %s, %s, %s)
        """
        # 执行SQL插入语句
        try:
            # 获取当前时间并格式化
            formatted_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            print("格式化的当前时间:", formatted_time)
            now = datetime.now()

            self.cursor.execute(sql_insert_message, (conversation_id, sender, content, now))
            self.connection.commit()  # 提交事务
            print("添加成功")
        except Exception as e:
            print(f"数据库操作发生了意外的错误: {e}")
            self.connection.rollback()  # 回滚事务

    #数据库读取函数
    def read_message(self, conversationID):
        # 构建SQL插入语句
        sql_query = """
                    SELECT sender, content 
                    FROM message 
                    WHERE conversation_id = %s 
                    ORDER BY id ASC
                """
        # 执行SQL插入语句
        try:
            self.cursor.execute(sql_query, (conversationID,))
            # 获取查询结果
            results = self.cursor.fetchall()

            return results

        except Exception as e:
            print(f"数据库操作发生了意外的错误: {e}")
            self.connection.rollback()  # 回滚事务

    # grpc客户请求接收函数
    def Ask(self, request, context):
        try:
            # 实现逻辑
            print(f"接收存档ID: {request.conversationID}，用户ID为: {request.userID}, 用户输入内容为: {request.content}")

            # 正常状态，且用户输入内容为空，不回复
            if request.content == "" and request.status == "0" :
                # 构造GRPC回复包
                response = AI_pb2.AIResponse(
                    code='0',
                    message="",
                    data=request.status
                )
                return response
            elif request.status == "1" :
                # 读档操作，①清空history
                self.history = []
                # ②重构history，查数据库拿scriptID，初始化剧本
                sql_role = "SELECT script_id FROM conversation WHERE id = %s"
                self.cursor.execute(sql_role, (request.conversationID,))
                # 获取单个结果
                scriptID = self.cursor.fetchone()
                if scriptID:
                    scriptID = scriptID[0]
                else:
                    raise ValueError("No script found for the given conversation ID")
                # condition = "id = " + request.conversationID
                # sql_role = f"SELECT script_id FROM conversation WHERE { condition }"
                # self.cursor.execute(sql_role)
                # scriptID = self.cursor.fetchall()


                script_result, role_result, scene_result = self.load_script_first(scriptID)
                if not script_result:
                    raise CustomError("Script not found")

                roles_description = "\n".join([f"角色: {role[0]}, 角色描述: {role[1]}, 性格: {role[2]}" for role in role_result])
                initial_scene = scene_result[0][0]
                initial_prompt = f"{script_result[0]}\n\n{roles_description}\n\n初始场景: {initial_scene}"

                self.history.append(
                    {"role": "system", "content": initial_prompt}
                )
                self.history.append(
                    {"role": "user", "content": "你将扮演文字冒险游戏的叙述者和游戏主控，负责描述场景、引导玩家并响应玩家的行动。"
                                                "你需要为玩家描述当前的场景和情节。"
                                                "等待玩家输入他们的行动或选择。"
                                                "根据玩家的输入，叙述故事的进展，描述结果和新的情境。"
                                                "每次回应后，可以提示玩家可以采取的下一步行动。"
                                                "现在请你依次向用户介绍背景，角色信息和初始场景，并询问用户的下一步动作"}
                )

                # ③循环读取message表历史记录，加入到history里
                result_1 = self.read_message(request.conversationID)

                for row in result_1:
                    sender_1 = row[0]  # 第1个字段是 sender
                    content_1 = row[1]  # 第2个字段是 content

                    if sender_1 == "AI" :
                        self.history.append(
                            {"role": "system", "content": content_1}
                        )
                    elif sender_1 == "user" :
                        self.history.append(
                            {"role": "user", "content": content_1}
                        )

                # ④ grpc 返回
                response = AI_pb2.AIResponse(
                    code='0',
                    message="",
                    data=request.status
                )
                return response

            else:
                # 正常状态，把user问题添加到数据库
                self.insert_message(request.conversationID, "user", request.content)

                # 向GPT发送请求
                ai_response = self.chat(request.content, self.history)
                print(f"GPT: {ai_response}")

                # AI回答添加到数据库
                self.insert_message(request.conversationID, "AI", ai_response)

                # 构造GRPC回复包
                response =  AI_pb2.AIResponse(
                    code='0',
                    message=ai_response,
                    data=request.status
                )
                return response
        except Exception as e:
            print(f"处理请求时出错: {e}")
            context.set_details('处理请求时出错')
            context.set_code(grpc.StatusCode.INTERNAL)
            return AI_pb2.AIResponse(
                code='1',
                message="出现了意外的错误！",
                data=request.status
                )

def serve():
    server = grpc.server(futures.ThreadPoolExecutor(max_workers=10))
    AI_pb2_grpc.add_AIServiceServicer_to_server(AIServiceServicer(), server)
    server.add_insecure_port('[::]:8082')
    server.start()
    print("服务器已启动，监听端口8082...")

    def signal_handler(sig, frame):
        print("正在关闭服务器...")
        server.stop(0)
        sys.exit(0)

    signal.signal(signal.SIGINT, signal_handler)
    signal.signal(signal.SIGTERM, signal_handler)

    server.wait_for_termination()

if __name__ == '__main__':
    serve()